alter           proc revenue_report_by_product

@product_Id int,
@defaultPrompt varchar(50),
@category varchar(15)

as
begin

/* variable to store the values */
	
	declare @part_Number varchar(50)
	declare @customer_Name varchar(65)
	declare @schedule_Date datetime
	declare @amount float
	declare @status varchar(50)
	declare @date_From varchar(10)
	declare @date_To varchar(10)
	declare @dateDiff datetime

	declare @month varchar(10)
	declare @sales_rep varchar(50)
	declare @end_user varchar(50)

	declare @LineItem_ID int
	declare @Unit_Price float	
	declare @Shipped_Qty int
	declare @Shipped_Amount float
		
	declare @revised_date datetime 
	declare @requested_date datetime 

	declare @req_date datetime

	declare @base_product_name varchar(50)
	declare @process_stepid bigint
	declare @product_type varchar(50)
	declare @product_type_order varchar(50)
	declare @finished_product_Id int

	declare @quantity  int
	declare @price float

	declare @lineItemStatus varchar(15)
	declare @lineItemCloseDate datetime
	declare @qty int
	declare @RMA_Requested_Date datetime
	declare @RMA_Close_Date     datetime
	
	declare @Is_Credit int
	SET @amount = 0
	SET @Is_Credit = 1
	declare @RmaEndUser varchar(50) 	

	set @month = '1'	

	/*set @date_From = CONVERT(varchar,CONVERT(datetime,@month+'/01/'+@year),101)
	set @date_To   = CONVERT(varchar,DATEADD(month,12,CONVERT(datetime,@date_From)),101)
	set @dateDiff  = CONVERT(datetime,DATEDIFF(day,1,@date_To),101)
	set @date_To   = CONVERT(varchar,@dateDiff,101)
	*/


	declare @results Table( ProductId int,ProductType varchar(50),PartNumber varchar(50),CustomerName varchar(65),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), SalesRep varchar(50), EndUser varchar(50), Quantity int, Price float)
	declare @final_results Table( ProductId int,ProductType varchar(50),PartNumber varchar(50),CustomerName varchar(65),RequestedScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), SalesRep varchar(50), EndUser varchar(50), Quantity int, Price float, Month int, QtStart int, QtEnd int, Year int)
	
	declare @CoolCursor CURSOR
	declare @CsolCursor CURSOR
	declare @RmaItemCursor cursor

	if (@product_Id <> '0')
	begin

		declare ProductCursor CURSOR for
		SELECT  P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND P.PRODUCT_ID = @product_Id
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		--AND (P_PARENT_PRODUCT_ID <> -1 OR P_TYPE = 'KIT' )
		AND PS_TYPE_NAME='FINISHED GOODS'
		ORDER BY P_Type_order, part_number
	end
	else if (@product_Id = '0')
	begin

		declare ProductCursor CURSOR for
		SELECT  P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		--AND (P_PARENT_PRODUCT_ID <> -1 OR P_TYPE = 'KIT' )
		AND PS_TYPE_NAME='FINISHED GOODS'
		ORDER BY P_Type_order, part_number
	end
	

		open ProductCursor 
		fetch next from ProductCursor
		into @process_stepid, @part_Number, @finished_product_Id, @product_type, @product_type_order
		while @@fetch_status = 0
		begin
			--  For Ship Details		
			SET @CsolCursor = CURSOR for
			select P.Part_Number, C.C_Name,
			CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
	                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
				END AS END_USER, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_Unit_Cost 		
			from Customer_Open_Order_LineItem COOL, 
			     customer_shipping_line_item_table CSLIT,
			     Product P, Sales_Order SO, Customer C ,
			     Customer_Shipping_Table CST
	
			where 
			--CST.CST_Ship_Date>=@date_From
			--and CST.CST_Ship_Date<=@date_To
			--and
			CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
			and CSLIT.LineItem_ID = COOL.LineItem_ID
			and COOL.Product_ID = @finished_product_Id
	                and COOL.Product_ID = P.Product_ID
			and COOL.SalesOrder_ID = SO.SalesOrder_ID
			and SO.SO_Is_Sample = 1
			--and SO.Customer_ID = @customer_Id
	                and SO.Customer_ID = C.Customer_ID 
			and CST.CST_STATUS = 'SHIPPED'

			-- For Backlog Details
			if (@category = 'REQUESTED')                
	        	begin
				SET @CoolCursor = CURSOR for
				select product.Part_Number,customer.C_Name,
				COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
	                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
				END AS END_USER, cool_qty, cool_unit_cost			
				from product, 
				     Customer_Open_Order_LineItem COOL, 
				     Sales_Order,Customer 
	
				where 
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
		                --and customer.Customer_ID= @customer_Id 
				and COOL.Product_ID= @finished_product_Id
				--and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
				--or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and (cool.cool_status = 'approved' or cool.cool_status = 'hold')
			end
			else if (@category = 'SCHEDULE')
			begin
				SET @CoolCursor = CURSOR for
				select product.Part_Number,
		        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
	                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
				END AS END_USER,		
	                        COOL.COOL_Requested_Date, cool_qty, cool_unit_cost	
				from product, 
				     Customer_Open_Order_LineItem COOL, 
				     Sales_Order,Customer 
	
				where 
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and COOL.Product_ID= @finished_product_Id
				--and customer.Customer_ID= @customer_Id
				--and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				--or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and (cool.cool_status = 'approved' or cool.cool_status = 'hold')	                 
			end

			-- for RMA Details
		/*	set @RmaItemCursor = cursor for
			select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date, End_User
			from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
			where 	
			RT.RMA_ID = RLIT.RMA_ID 
			and RMA_Status <> 'CANCELLED'
			and PRO.Part_Number = RLIT.Part_Number
			and PRO.ProcessStep_ID = PS.ProcessStep_ID
			and PS.PS_Type_Name = 'Finished Goods'
			and PRO.Product_ID = @finished_product_Id
			and RT.Customer_Name = CUST.C_Name
			--and CUST.Customer_ID = @customer_Id
		*/
			
			open @CsolCursor 
				fetch next from @CsolCursor
				into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price
				while @@fetch_status = 0
				begin
					if(@amount is null)
					begin
						set @amount = 0
					end
		
					if(@schedule_Date is null OR @schedule_Date= '')
					begin
						set @schedule_Date = @requested_date
					end
		
					insert into @results(ProductId,ProductType,PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser, Quantity, Price) values(@finished_product_Id,@product_type,@part_Number,@customer_Name,@schedule_Date, @amount, @status, 'COOL', @sales_rep, @end_user, @quantity, @price) 	
					set @part_number = null
					set @customer_name = null
					set @schedule_date = null
					set @amount = null
					set @status = null
					set @requested_date = null
		
					fetch next from @CsolCursor into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price
				end
			close @CsolCursor
			deallocate @CsolCursor
		
		
			open @CoolCursor 
				if (@category = 'REQUESTED')                
		        	begin
				fetch next from @CoolCursor
				into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price
				end
				else if (@category = 'SCHEDULE')
				begin
				fetch next from @CoolCursor
				into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date, @quantity, @price
				end	
				
				while @@fetch_status = 0
				begin
							
					if(@amount is null)
					begin
						set @amount = 0
					end
					
					if (@category = 'REQUESTED')                
		        		begin
						
						if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
						begin
							set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
						end
						else
						begin
							set @schedule_Date = @schedule_Date
						end
					end	
			
					if (@category = 'SCHEDULE')
					begin
						if(@schedule_Date is null OR @schedule_Date= '')
						begin
							set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
						end
						else
						begin
							set @schedule_Date = @schedule_Date
		
							if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
							begin
								set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
							end
							else
							begin
								set @schedule_Date = @schedule_Date
							end
						end
					end
					
					if (@category = 'SCHEDULE')
					begin
						if(@revised_date is not null OR @revised_date <> '')
						begin
							set @schedule_Date = @revised_date
							
							if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
							begin
								set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
							end
							else
							begin
								set @schedule_Date = @schedule_Date
							end
		
							
						end
					end
		
					select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
					
					set @amount = @amount - (@Unit_Price * @Shipped_Qty)	
					if ( @amount < 0 )
					begin
						set @amount = 0	
					end
					set @quantity = @quantity - @shipped_qty
					if ( @quantity  < 0)
					begin
						set @quantity = 0
					end
		
					insert into @results(ProductId,ProductType,PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser, Quantity, Price) values(@finished_product_Id,@product_type,@part_Number,@customer_Name,@schedule_Date, @amount ,@status, 'COOL', @sales_rep, @end_user, @quantity, @price ) 	
		
					set @part_number = null
					set @customer_name = null
					set @schedule_date = null
					set @amount = null
					set @status = null
					set @LineItem_ID = null
					set @unit_price = null
					set @requested_date = null
					set @revised_date = null
					
					if (@category = 'REQUESTED')                
			        	begin
					fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price
					end
					else if (@category = 'SCHEDULE')                
			        	begin
					fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date, @quantity, @price
					end
					
				end
				close @CoolCursor
				deallocate @CoolCursor
				
			/*	open @RmaItemCursor
				fetch next from @RmaItemCursor into @customer_Name , @part_Number , @status , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate, @RmaEndUser
				while @@fetch_status = 0
				begin
					if ( @lineItemStatus = 'CLOSE' )
					begin
						set @status = 'CLOSE'
						set @RMA_Close_Date = @lineItemCloseDate
					end
			
			
					if (@status = 'ISSUED')
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
						set @status = 'APPROVED'
					end
					else if (@status = 'CLOSE')
					begin
						set @schedule_Date = @RMA_Close_Date
						set @status = 'SHIPPED'
					end
			
					-- If Credit is selected, then only we need to show the amount. Otherwise, amount should be zero. 
					-- (Is_Credit = 0 ==> Credit Selected & Is_Credit = 1 ==> Credit Not Selected)
					IF( @Is_Credit = 1)
					BEGIN
						SET @amount = 0
					END
					ELSE IF (@Is_Credit = 0)
					BEGIN
						SET @amount = @qty * @unit_price 
					END
					
			
					insert into @results(ProductId,ProductType,PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser, Quantity, Price) values(@finished_product_Id,@product_type,@part_Number,@customer_Name,@schedule_Date, @amount,@status,'RMA', '', @RmaEndUser, @qty , @Unit_Price ) 		
					
					SET @amount = 0
					SET @Is_Credit = 1
					
					set @customer_Name = null
					set @part_Number = null
					set @status = null
					set @RMA_Requested_Date = null
					set @RMA_Close_Date = null
					set @qty = null
					set @Unit_Price = null
					set @Is_Credit = null
			
					fetch next from @RmaItemCursor into @customer_Name , @part_Number , @status , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate, @RmaEndUser
				end	
					
				close @RmaItemCursor
				deallocate @RmaItemCursor
			*/
		fetch next from ProductCursor
		into @process_stepid, @part_Number, @finished_product_Id, @product_type, @product_type_order
		
	end
	close ProductCursor
	deallocate ProductCursor	
 

	
	declare @FinalCursor CURSOR
	
	declare @month_number int
	declare @quarter_start int
	declare @quarter_end int
	declare @act_year int

	declare @pId int
	declare @pType varchar(50)
	declare @pNumber varchar(50)
	declare @cName varchar(50)
	declare @revDate datetime
	declare @revAmount float
	declare @revStatus varchar(15)
	declare @revType varchar(5)
	declare @sRep varchar(50)
	declare @eUser varchar(50)
	declare @revQuantity int
	declare @revUnitPrice float

			set @FinalCursor = cursor for 
			select * from @results

			open @FinalCursor 
				fetch next from @FinalCursor
				into @pId,@pType, @pNumber, @cName, @revDate, @revAmount, @revStatus, @revType, @sRep, @eUser, @revQuantity, @revUnitPrice
				while @@fetch_status = 0
				begin
	
		 			select @act_year = DATEPART(yyyy, @revDate) 
		
		 			select @month_number = DATEPART(mm, @revDate) 
		 			
		 			if (@month_number >=1 and @month_number <=3)
		 			begin
		 				set @quarter_start = 1
		 				set @quarter_end =3
		 				
		 			end
		 			else if (@month_number >=4 and @month_number <=6)
		 			begin
		 				set @quarter_start = 2
		 				set @quarter_end =6
				
		 			end
		 			else if (@month_number >=7 and @month_number <=9)
		 			begin
		 				set @quarter_start = 3
		 				set @quarter_end =9
		  				
		 			end						
		 			else if (@month_number >=10 and @month_number <=12)
		 			begin
		 				set @quarter_start = 4
		 				set @quarter_end =12
		  			end

					insert into @final_results values( @pId, @pType, @pNumber, @cName, @revDate, @revAmount, @revStatus, @revType, @sRep, @eUser, @revQuantity, @revUnitPrice, @month_number, @quarter_start, @quarter_end, @act_year )					

					fetch next from @FinalCursor
					into @pId,@pType, @pNumber, @cName, @revDate, @revAmount, @revStatus, @revType, @sRep, @eUser, @revQuantity, @revUnitPrice
 		
				end

			close @FinalCursor
			deallocate @FinalCursor

	select * from @final_results

end


